insert overwrite table res_oil
select substr(X.transaction_date,1,16),X.station_id,X.product_code,count(X.product_code),sum(X.item_quantity),sum(X.item_value)
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.product_code,Y.item_quantity,Y.item_value
from mid.transactions as Z
join mid.transaction_items_oil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
where Y.item_type!=20 and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600)AS X
group by substr(X.transaction_date,1,16),X.station_id,X.product_code;


insert overwrite table res_notoil
select SUBSTR(transaction_date,1,16),station_id,count(DISTINCT transaction_id),sum(X.item_value)
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value
from mid.transactions as Z
join mid.transaction_items_notoil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
where unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600)AS X
group by SUBSTR(transaction_date,1,16),station_id;

insert overwrite table TBL_ORDERDETAILS_ALL
select ORDERDETAILS_ID    ,
VERSION            ,
NOWDATE            ,
VIRTUALGOODSID     ,
RETURNCODE         ,
LITRE              ,
if(STATIONCODE = '58008','58010',STATIONCODE)       ,
COUPONAMOUNT       ,
REQUESTID          ,
ORDERNUM           ,
REALAMOUNT         ,
OILCODE            ,
ORDER_DEAL         ,
DISCOUNTAMOUNT     ,
CARDUSERID         ,
AMOUNT             ,
OILPRICE           ,
TERMNUM            ,
SENDTIME           ,
INVOICETITLE       ,
MEMBERNO           ,
VIRTUALGOODSIDS    ,
CUSTOMER_ID        ,
create_shijian     ,
PPSORDERNUMBER     ,
GOODSAMOUNT        ,
FUELCOUPONAMOUNT   ,
GOODCOUPONAMOUNT   ,
GOODSCOUPONAMOUNT  ,
UPDATEREALAMOUNT   ,
FULECOUPONAMOUNT   ,
FULEAMOUNT         ,
CARLICENSE         ,
OILGUNNO           ,
ORDER_STATE        ,
EPSCODE  from TBL_ORDERDETAILS union all
select  ORDERDETAILS_ID    ,
VERSION            ,
NOWDATE            ,
VIRTUALGOODSID     ,
RETURNCODE         ,
LITRE              ,
if(STATIONCODE = '58008','58010',STATIONCODE)       ,
COUPONAMOUNT       ,
REQUESTID          ,
ORDERNUM           ,
REALAMOUNT         ,
OILCODE            ,
ORDER_DEAL         ,
DISCOUNTAMOUNT     ,
CARDUSERID         ,
AMOUNT             ,
OILPRICE           ,
TERMNUM            ,
SENDTIME           ,
INVOICETITLE       ,
MEMBERNO           ,
VIRTUALGOODSIDS    ,
CUSTOMER_ID        ,
create_shijian     ,
PPSORDERNUMBER     ,
GOODSAMOUNT        ,
FUELCOUPONAMOUNT   ,
GOODCOUPONAMOUNT   ,
GOODSCOUPONAMOUNT  ,
UPDATEREALAMOUNT   ,
FULECOUPONAMOUNT   ,
FULEAMOUNT         ,
CARLICENSE         ,
OILGUNNO           ,
ORDER_STATE        ,
EPSCODE from TBL_ORDERDETAILS_CD;

insert overwrite table res_oil_vip
select substr(SENDTIME,1,16),trim(STATIONCODE),trim(OILCODE),COUNT(*),SUM(LITRE)/1000,SUM(REALAMOUNT)/100
FROM TBL_ORDERDETAILS_ALL WHERE LITRE != 0 and ORDER_DEAL = 4 GROUP BY substr(SENDTIME,1,16),trim(STATIONCODE),trim(OILCODE);

insert overwrite table res_notoil_vip
select substr(SENDTIME,1,16),STATIONCODE,COUNT(*),SUM(AMOUNT)/100
FROM TBL_ORDERDETAILS_ALL WHERE LITRE = 0 and ORDER_DEAL = 4 GROUP BY substr(SENDTIME,1,16),STATIONCODE,OILCODE;



INSERT OVERWRITE TABLE MID_addVip 
SELECT EFFECTIVEDATE,COUNT(CARD_ID)AS COUNT 
FROM TBL_CARD 
GROUP BY EFFECTIVEDATE;

INSERT OVERWRITE TABLE RES_addVip 
select date,count,sum(count) over(order by date) 
from mid_addvip;

INSERT OVERWRITE TABLE MID_addVip_CD 
SELECT EFFECTIVEDATE,COUNT(CARD_ID)AS COUNT 
FROM TBL_CARD_CD 
GROUP BY EFFECTIVEDATE;

INSERT OVERWRITE TABLE RES_addVip_CD 
select date,count,sum(count) over(order by date) 
from mid_addvip_CD;

insert overwrite table res_recharge 
select CREATEDATE,count(AMOUNT) AS tradeNumber,sum(AMOUNT) as totalAmount 
from TBL_ORDER where TRADEID > 0
group by CREATEDATE;

insert overwrite table res_recharge_cd
select CREATEDATE,count(AMOUNT) AS tradeNumber,sum(AMOUNT) as totalAmount 
from TBL_ORDER_CD where TRADEID > 0 
group by CREATEDATE;

insert overwrite table mid_vipEvaluation
select to_date(Y.time),
Z.OLDUNIQUEID,
count(Y.evaluation_id),
SUM(Y.STAR),
SUM(Y.STAR2),
SUM(Y.STAR3),
SUM(Y.STAR4),
SUM(Y.STAR5)
FROM TBL_EVALUATION AS Y join TBL_ORGANIZATION AS Z ON Y.ORG_ID=Z.ID
GROUP BY to_date(Y.time),Z.OLDUNIQUEID;



INSERT OVERWRITE TABLE res_vipEvaluation
select a.date,a.stationid,b.sum,a.dealnumber,
if(a.star1total is null,0,star1total),if(a.star2total is null,0,star2total),
if(a.star3total is null,0,star3total),if(a.star4total is null,0,star4total),
if(a.star5total is null,0,star5total)
from mid_vipEvaluation as a
join 
(select substr(minutes,1,10) as days,stationid,sum(oilnumber)as sum from res_oil_vip group by substr(minutes,1,10),stationid) as b
on a.date = b.days and a.stationid = b.stationid;




insert overwrite table mid_vipEvaluation_cd
select to_date(Y.time),
if(Z.OLDUNIQUEID = '58008','58010',OLDUNIQUEID),
count(Y.evaluation_id),
SUM(Y.STAR),
SUM(Y.STAR2),
SUM(Y.STAR3),
SUM(Y.STAR4),
SUM(Y.STAR5)
FROM TBL_EVALUATION_CD AS Y 
join TBL_ORGANIZATION_CD AS Z 
ON Y.ORG_ID=Z.ID
GROUP BY to_date(Y.time),Z.OLDUNIQUEID;


INSERT OVERWRITE TABLE res_vipEvaluation_cd
select a.date,a.stationid,b.sum,a.dealnumber,
if(a.star1total is null,0,star1total),if(a.star2total is null,0,star2total),
if(a.star3total is null,0,star3total),if(a.star4total is null,0,star4total),
if(a.star5total is null,0,star5total)
from mid_vipEvaluation_cd as a
join 
(select substr(minutes,1,10) as days,stationid,sum(oilnumber)as sum from res_oil_vip group by substr(minutes,1,10),stationid) as b
on a.date = b.days and a.stationid = b.stationid;




insert overwrite table res_vipEvaluation_all
select * from res_vipEvaluation 
union all 
select * from res_vipEvaluation_cd;
